/**
 * Project: topdt-jbpm
 * Copyright 2013  Top Digital Technology  Co. Ltd.
 * All right reserved.
 */
package com.topdt.jbpm.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.thx.common.orm.PaginationDao;
import com.thx.common.util.AppContext;
import com.topdt.jbpm.model.Work;

/**
 * 建立日期 : 2013-4-25 下午12:03:16<br>
 * 作者 : balance<br>
 * 模块 : <br>
 * 描述 : <br>
 * 修改历史: 序号 日期 修改人 修改原因 <br>
 * 1 <br>
 * 2 <br>
 */
@Repository
public class WorkDao extends PaginationDao<Work> {

	//--查找工作
	@SuppressWarnings("unchecked")
	public List<Work> listWork(String starter,String state) {

		String hql = "from Work where starter =? and workState = ? order by startTime desc";
		List<Work> list = createQuery(hql, new Object[] { starter,state}).list();
		if (list.size() > 0)
			return list;
		return null;
	}

	/**
	 * 查询分类列表页
	 * 建立日期 : Apr 18, 2013 <br>
	 * 作者 : 王阿敏<br>
	 * @param info
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Work> getAll(Work info,	final int start, final int limit) {
		String comId = AppContext.getCurrentComId();
		String hql = getWorkHql(info,"",comId);
		List<Work> list = createQuery(hql).setFirstResult(start).setMaxResults(limit).list();
		return list;
	}
	/**
	 * 查询分类数
	 * 建立日期 : Apr 18, 2013 <br>
	 * 作者 : 王阿敏<br>
	 * @param info
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public int queryCount(Work info) {
		String comId = AppContext.getCurrentComId();
		String hql = "select count(*) " + getWorkHql(info,"",comId);
		Object  obj =  createQuery(hql).uniqueResult();
		return ((Long)obj).intValue();
	}

	/**
	 * 查询通信录分类sql语句
	 * 建立日期 : Apr 18, 2013 <br>
	 * 作者 : 王阿敏<br>
	 * @param info
	 * @return
	 */
	/*public String getHql(Work info){
		String hql="from Work c where 1=1";
		if(StringUtils.isNotBlank(info.getStarter())){
			hql+="c.starterId ="+info.getStarter();
		}
		if(StringUtils.isNotBlank(info.getWorkState())){
			hql+="c.workState ="+info.getWorkState();
		}
		hql+="order by c.startTime desc";
		return hql;
	}*/


	public List<Work> findByJbpmFlowKey(String jbpmFlowKey) {
		String comId = AppContext.getCurrentComId();
		String hql = "from Work w where w.companyId = ? and w.flowKey = ?";
		return createQuery(hql, new Object[]{comId, jbpmFlowKey}).list();
	}

	public List<Work> findByJbpmFlowIdAndKey(String jbpmFlowId, String jbpmFlowKey) {
		String comId = AppContext.getCurrentComId();
		String hql = "from Work w where w.companyId = ? and w.flowId = ? and w.flowKey = ?";
		return createQuery(hql, new Object[]{comId, jbpmFlowId, jbpmFlowKey}).list();
	}

	/**
	 * 通过workId查找起草人
	 * @author 刘杰
	 * @date   Aug 1, 2014 9:44:57 AM
	 * @param workId
	 * @return
	 */
	public String findStarterNameByWorkId(String workId) {
		String hql = "from Work where workId = ?";
		List<Work> list = createQuery(hql, workId).list();
		if(list.size()>0){
			return list.get(0).getStarterName();
		}else{
			return "";
		}

	}
	/**
	 *
	 * <b>Method countWorkByCondition</b>
	 * <dd>方法作用：(统计当前年份已经存在的work的数量)
	 * @param year 当前年份
	 * @param companyId 企业id
	 * @param appName	应用名称
	 * @author Wangxufeng
	 * @date 2014-12-02
	 * @return
	 * @since Met 1.0
	 */
	/*public Long countWorkByCondition(String year,String companyId,String appName){
		String sql = "select count(*) from wf_work where COMPANY_ID=? and APP_NAME=? and to_char(START_TIME,'yyyy') = ? and WORK_STATE !='"+Work.STATE_START+"'";
		Object count = createSQLQuery(sql, companyId,appName,year).uniqueResult();
		return Long.parseLong(count.toString());
	}*/
	/**
	 * 重写update方法
	 * wangxufeng add 2014-12-09
	 * modify by calonlan
	 * 没有发现Work的seqNum必要性，暂时性注释
	 */
	/*@Override
	public void update(Object entity) {
		if(entity instanceof Work){
			Work work = (Work)entity;
			if( !work.getWorkState().equals(Work.STATE_START) && StringUtils.isBlank(work.getSeqNum())){
				Calendar calendar = Calendar.getInstance();
				String year = calendar.get(Calendar.YEAR)+"";
				Long workNum = countWorkByCondition(year,work.getCompanyId(), work.getAppName());
				String nextWorkNum = (workNum+1)+"";
				String seqNum = "";
				for (int i = 0; i < 5-nextWorkNum.length(); i++) {
					seqNum +="0";
				}
				seqNum = year + seqNum + nextWorkNum;
				work.setSeqNum(seqNum);
			}
		}
		super.update(entity);
	}*/



	public List<Work> getAll(Work info, String appAttr) {
		String comId = AppContext.getCurrentComId();
		String hql = getWorkHql(info, appAttr,comId);
		List<Work> list = this.findPageList(hql);
		return list;
	}
	/**
	 * 客户端查询公文工单的草稿，我的起草工作
	 * @author zhangyong
	 * @date   2015年12月23日 下午2:52:26
	 * @param info
	 * @param appAttr
	 * @param start
	 * @param limit
	 * @return
	 */
	public List<Work> getAll(Work info, String appAttr ,final int start, final int limit) {
		String comId = AppContext.getCurrentComId();
		String hql = getWorkHql(info, appAttr,comId);
		List<Work> list = this.createQuery(hql).setFirstResult(start).setMaxResults(limit).list();
		return list;
	}
	/**
	 * 查询分类数 建立日期 : Apr 18, 2013 <br>
	 * 作者 : 王阿敏<br>
	 *
	 * @param info
	 * @param page
	 * @return
	 */
	/*@SuppressWarnings("unchecked")
	public int queryCount(Work info, String isExpand) {
		String hql = "select count(*) " + getWorkHql(info, isExpand);
		List list = createQuery(hql).list();
		if (list.size() > 0) {
			Long count = (Long) list.get(0);
			return new Integer(count + "");
		}
		return 0;
	}*/

	/**
	 * 查询我的工作sql语句 建立日期 : Apr 25, 2013 <br>
	 * 作者 : 王阿敏<br>
	 *
	 * @param info
	 * @return
	 */
	public String getHql(Work info) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "c.companyId='" + comId + "' and ";

		String hql = "from Work c where " + condition + " 1=1 ";
		if (StringUtils.isNotBlank(info.getStarter())) {
			hql += " and c.starter = '" + info.getStarter() + "'";
		}
		if (info.getAppId() != null && !"".equals(info.getAppId())) {
			hql += " and c.appId = '" + info.getAppId() + "'";
		}
		if (info.getWorkTitle() != null && !"".equals(info.getWorkTitle())) {
			hql += " and c.workTitle like '%" + info.getWorkTitle() + "%'";
		}
		if (StringUtils.isNotBlank(info.getWorkState())) {
			if (info.getWorkState().equals(Work.STATE_START)) {
				hql += " and c.workState = '" + info.getWorkState() + "'";
			} else {
				hql += " and c.workState<>'" + Work.STATE_START + "'";
			}
		}
		hql += " and not exists (select 1 from Application app where app.isInherent = 1 )";
		hql += " order by c.startTime desc";
		return hql;
	}

	/**
	 * 建立日期 : Aug 5, 2013 6:41:41 PM<br>
	 * 作者: 孙耀飞<br>
	 * 模块: 查询我的起草 <br>
	 * 描述: <br>
	 * 1 <br>
	 * 2 <br>
	 */
	public String getWorkHql(Work info, String appAttr,String comId) {

		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "w.companyId='" + comId + "' and ";

		String hql = "from Work w where " + condition + " 1=1 ";
		if(StringUtils.isNotBlank(info.getPerIds())){
			hql += " and w.starter in (" + info.getPerIds() + ")";
		}
		if (StringUtils.isNotBlank(info.getStarter())) {
			hql += " and w.starter = '" + info.getStarter() + "'";
		}
		if (StringUtils.isNotBlank(info.getAppId()) && !info.getAppId().equals("null")) {
			if(info.getAppId().indexOf(",")==-1){
				hql += " and w.appId = '" + info.getAppId() + "'";
			}else{
				hql += " and w.appId in(" + info.getAppId() + ")";
			}
		}
		if (StringUtils.isNotBlank(info.getWorkTitle())) {
			hql += " and w.workTitle like '%" + info.getWorkTitle() + "%'";
		}
		if (StringUtils.isNotBlank(info.getWorkState())) {
			if (info.getWorkState().equals(Work.STATE_START)) {
				hql += " and w.workState = '" + info.getWorkState() + "'";
			} else{
				hql += " and w.workState<>'" + Work.STATE_START + "'";
			}
		}else{
			hql += " and w.workState<>'" + Work.STATE_START + "'";
		}
		if("document".equalsIgnoreCase(appAttr)){
			hql += " and w.appId in (select a.appId from Application a where (a.appAttr='1' or a.appAttr='3' or a.appAttr='5') and a.isValid='1' )";
		}else if("ticket".equalsIgnoreCase(appAttr)){
			hql += " and w.appId in (select a.appId from Application a where (a.appAttr='0' or a.appAttr='2' or a.appAttr='4') and a.isValid='1' )";
		}
		/**
		 * wangxufeng add 2014-11-26
		 */
		if(StringUtils.isNotBlank(info.getSeqNum())){
			hql += " and w.seqNum like '%" + info.getSeqNum() + "%'";
		}
		if(StringUtils.isNotBlank(info.getStartDeptId())){
			hql += " and w.starter in (select p.userId from  User p where p.deptId ='"
					+ info.getStartDeptId() + "')";
		}
		if(StringUtils.isNotBlank(info.getStarterName())){
			hql += " and w.starterName like '%" + info.getStarterName() + "%'";
		}
		if(StringUtils.isNotBlank(info.getFirstTime())){
			hql += " and w.startTime >= to_date('" + info.getFirstTime() + "','yyyy-MM-dd HH24:mi:ss')";
		}
		if(StringUtils.isNotBlank(info.getLastTime())){
			hql += " and w.startTime <= to_date('" + info.getLastTime() + "','yyyy-MM-dd HH24:mi:ss')";
		}
		// add over
		/**
		 * add by lcx 删除状态
		 */
//		if("1".equals(state)){
//			hql += " and w.state = '1'";
//		}else{
//			hql += " and w.state is null ";
//		}
		if(StringUtils.isNotBlank(info.getIsDelete())){
			hql += " and w.isDelete = '"+info.getIsDelete()+"' ";
		}else{
			hql += " and w.isDelete is null ";
		}
		// add over
		hql += " order by w.startTime desc";
		return hql;
	}

	@SuppressWarnings("unchecked")
	public List<Work> getWorklist(Work work, Long personnelId, int start, int max) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "w.company_id='" + comId + "' and ";

		String workTitle = "";
		if (work != null && !"".equals(work) && work.getWorkTitle() != null && !"".equals(work.getWorkTitle())) {
			workTitle = " w.work_title like '%" + work.getWorkTitle() + "%' and ";
		}
		String perId = String.valueOf(personnelId);
		String sql = "select * from wf_work w "
				+ " where "
				+ condition
				+ workTitle
				+ "w.starter<> ? "
				+ " and exists (select  t.work_id from wf_hist_todo t where t.to_user = ? and t.work_id=w.work_id )"
				+ " and not exists (select 1 from wf_application app where app.IS_INHERENT = '1' and app.app_id=w.app_id )"
				+ " order by w.start_time desc";
		List<Work> list = createSqlQuery(sql, perId, perId).addEntity(Work.class).setFirstResult(start)
				.setMaxResults(max).list();
		if (list.size() > 0) {
			return list;
		} else {
			return null;
		}
	}

	public int queryWorklistCount(Work work, Long personnelId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "w.company_id='" + comId + "' and ";

		String workTitle = "";
		if (work != null && !"".equals(work) && work.getWorkTitle() != null && !"".equals(work.getWorkTitle())) {
			workTitle = " w.work_title like '%" + work.getWorkTitle() + "%' and ";
		}
		String perId = String.valueOf(personnelId);
		String sql = "select count(1) from wf_work w "
				+ " where "
				+ condition
				+ workTitle
				+ " w.starter<> ? and "
				+ " exists (select  t.work_id from wf_hist_todo t where t.to_user = ? and t.work_id=w.work_id )"
				+ " and not exists (select 1 from wf_application app where app.IS_INHERENT = '1'  and app.app_id=w.app_id)"
				+ " order by w.start_time desc";
		List list = createSqlQuery(sql, perId, perId).list();
		if (list.size() > 0) {
			java.math.BigDecimal count = (java.math.BigDecimal) list.get(0);
			return count.intValue();
		}
		return 0;
	}



	public int queryWorklistCountByAppId(Work work, Long personnelId, String appId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "w.company_id='" + comId + "' and ";

		String workTitle = "";
		if (work != null && !"".equals(work) && work.getWorkTitle() != null && !"".equals(work.getWorkTitle())) {
			workTitle = " w.work_title like '%" + work.getWorkTitle() + "%' and ";
		}
		String perId = String.valueOf(personnelId);
		String sql = "select count(*) from wf_work w " + " where " + condition + workTitle
				+ " w.starter<> ? and w.app_id=? and "
				+ " exists (select  t.work_id from wf_hist_todo t where t.to_user = ? and t.work_id=w.work_id )"
				+ " and not exists (select 1 from wf_application app where app.IS_INHERENT = 1 )"
				+ " order by w.start_time desc";
		List list = createSqlQuery(sql, perId, appId, perId).list();
		if (list.size() > 0) {
			java.math.BigDecimal count = (java.math.BigDecimal) list.get(0);
			return count.intValue();
		}
		return 0;
	}


	public List<Work> getWorkPage(String toUserId,String workTitle,String type) {
		String comId = AppContext.getCurrentComId();
		String hql = "from Work w where w.companyId = ?"
				+ " and w.workId in( select t.workId from HistTodo t where t.toUser = ? and t.fromUser is not null) ";
		if("ticket".equalsIgnoreCase(type)){
			hql += " and w.appId not in (select a.appId from Application a where (a.appAttr='1' or a.appAttr='3' or a.appAttr='5' ) and a.isValid='1' )";
		}else if("document".equalsIgnoreCase(type)){
			hql += " and w.appId in (select a.appId from Application a where (a.appAttr='1' or a.appAttr='3' or a.appAttr='5' )  and a.isValid='1' )";
		}
		if (workTitle!=null) {
			hql+=" and w.workTitle like '%" + workTitle + "%'";
		}
		hql += " order by w.startTime desc";
		List<Work> workList = this.findPageList(hql, comId, toUserId);
		return workList;
	}


	/**
	 * 重载getWorkPage方法，根据appId查找已办工作
	 * @author 冯晋首
	 * @date   2015年12月21日 下午5:03:52
	 * @param toUserId
	 * @param workTitle
	 * @param type
	 * @param appId
	 * @return
	 */
	public List<Work> getWorkPage(String toUserId,String workTitle,String type,String appId) {
		String comId = AppContext.getCurrentComId();
		String hql = "from Work w where w.companyId = ?"
				+ " and w.workId in( select t.workId from HistTodo t where t.toUser = ? and t.fromUser is not null) ";
		if("ticket".equalsIgnoreCase(type)){
			hql += " and w.appId in (select a.appId from Application a where (a.appAttr='0' or a.appAttr='2' or a.appAttr='4' )  and a.isValid='1' )";
		}else if("document".equalsIgnoreCase(type)){
			hql += " and w.appId in (select a.appId from Application a where (a.appAttr='1' or a.appAttr='3' or a.appAttr='5' )  and a.isValid='1' )";
		}
		if(StringUtils.isNotBlank(appId)){
			if(appId.indexOf(",")==-1){
				hql+=" and w.appId = '"+appId+"' ";
			}else{
				hql+=" and w.appId in ("+appId+") ";
			}
		}
		if (workTitle!=null) {
			hql+=" and w.workTitle like '%" + workTitle + "%'";
		}
		hql += " order by w.startTime desc";
		List<Work> workList = this.findPageList(hql, comId, toUserId);
		return workList;
	}
	/**
	 * 客户端分页查询已办工作公文工单通用
	 * @author zhangyong
	 * @date   2015年12月23日 下午2:33:29
	 * @param toUserId
	 * @param workTitle
	 * @param type
	 * @param appId
	 * @param start
	 * @param max
	 * @return
	 */
	public List<Work> getWorkPage(String toUserId,String workTitle,String type,String appId,int start, int max) {
		String comId = AppContext.getCurrentComId();
		String hql = "from Work w where w.companyId = ?"
				+ " and w.workId in( select t.workId from HistTodo t where t.toUser = ? and t.fromUser is not null) ";
		if("ticket".equalsIgnoreCase(type)){
			hql += " and w.appId  in (select a.appId from Application a where (a.appAttr='0' or a.appAttr='2' or a.appAttr='4' )  and a.isValid='1' )";
		}else if("document".equalsIgnoreCase(type)){
			hql += " and w.appId in (select a.appId from Application a where (a.appAttr='1' or a.appAttr='3' or a.appAttr='5' )  and a.isValid='1' )";
		}
		if(StringUtils.isNotBlank(appId)&&(!appId.equals("null"))){
			//fengjinshou 20160217
			if(appId.indexOf(",")==-1){
				hql+=" and w.appId = '"+appId+"' ";
			}else{
				hql+=" and w.appId in ("+appId+") ";
			}
		}
		if (workTitle!=null) {
			hql+=" and w.workTitle like '%" + workTitle + "%'";
		}
		hql+=" and w.workState='"+Work.STATE_END+"'";
		hql += " order by w.startTime desc";
		List<Work> workList = this.createQuery(hql, new Object[] { comId, toUserId }).setFirstResult(start).setMaxResults(max).list();
		return workList;
	}


	/**
	 * 客户端，查找正在办理的公文或工单
	 * @author 冯晋首
	 * @date   2016年2月17日 下午4:47:02
	 * @param toUserId
	 * @param workTitle
	 * @param type
	 * @param appId
	 * @param start
	 * @param max
	 * @return
	 */
	public List<Work> getDoingWork(String toUserId,String workTitle,String type,String appId,int start, int max) {
		String comId = AppContext.getCurrentComId();
		String hql = "from Work w where w.companyId = ?"
				+ " and w.workId in( select t.workId from HistTodo t where t.toUser = ?) ";
		if("ticket".equalsIgnoreCase(type)){
			hql += " and w.appId  in (select a.appId from Application a where (a.appAttr='0' or a.appAttr='2' or a.appAttr='4' )  and a.isValid='1' )";
		}else if("document".equalsIgnoreCase(type)){
			hql += " and w.appId in (select a.appId from Application a where (a.appAttr='1' or a.appAttr='3' or a.appAttr='5' )  and a.isValid='1' )";
		}
		if(StringUtils.isNotBlank(appId)&&(!appId.equals("null"))){
			//fengjinshou 20160217
			if(appId.indexOf(",")==-1){
				hql+=" and w.appId = '"+appId+"' ";
			}else{
				hql+=" and w.appId in ("+appId+") ";
			}
		}
		if (workTitle!=null) {
			hql+=" and w.workTitle like '%" + workTitle + "%'";
		}
		hql+=" and w.workState='"+Work.STATE_RUN+"'";
		hql += " order by w.startTime desc";
		List<Work> workList = this.createQuery(hql, new Object[] { comId, toUserId }).setFirstResult(start).setMaxResults(max).list();
		return workList;
	}

	/**
	 * 建立日期 : Aug 5, 2013 5:36:35 PM<br>
	 * 作者: 孙耀飞<br>
	 * 模块: 查询已办的work集合 ，分页查询<br>
	 * 描述: work,实体; personnelId,人员Id; appId,应用Id<br>
	 * 1 <br>
	 * 2 <br>
	 */
	@SuppressWarnings("unchecked")
	public List<Work> getWorkPage(Work work, Long personnelId, String appId, String isExpand, int start, int max) {
		String comId = AppContext.getCurrentComId();
		String workTitle = "";
		if (StringUtils.isNotBlank(work.getWorkTitle())) {
			workTitle = " and w.work_title like '%" + work.getWorkTitle() + "%'";
		}
		String perId = String.valueOf(personnelId);
		String sql = "select * from wf_work w where w.company_id = ?  ";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			sql += " and w.app_id =?";
		}
		sql += workTitle + " and w.work_id in("
				+ "  select t.work_id from wf_hist_todo t where t.to_user = ? and t.from_user is not null) ";
		if (StringUtils.isNotBlank(isExpand) && isExpand.equals("yes")) {
			sql += " and w.app_id not in (select a.app_id from wf_application a where a.is_inherent ='1' and a.company_id='"
					+ comId + "')";
		}
		sql += " order by w.start_time desc";
		List<Work> list = new ArrayList<Work>();
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			list = createSqlQuery(sql, new Object[] { comId, appId, perId }).addEntity(Work.class)
					.setFirstResult(start).setMaxResults(max).list();
		} else {
			list = createSqlQuery(sql, new Object[] { comId, perId }).addEntity(Work.class).setFirstResult(start)
					.setMaxResults(max).list();
		}

		if (list.size() > 0) {
			return list;
		} else {
			return null;
		}
	}

	/**
	 * 建立日期 : Aug 5, 2013 5:36:35 PM<br>
	 * 作者: 孙耀飞<br>
	 * 模块: 查询已办的work集合 ，分页查询<br>
	 * 描述: work,实体; personnelId,人员Id; appId,应用Id<br>
	 * 1 <br>
	 * 2 <br>
	 */
	@SuppressWarnings("unchecked")
	public int getWorkCount(Work work, Long personnelId, String appId, String isExpand) {
		String comId = AppContext.getCurrentComId();

		String workTitle = "";
		if (StringUtils.isNotBlank(work.getWorkTitle())) {
			workTitle = " and w.work_title like '%" + work.getWorkTitle() + "%'";
		}
		String perId = String.valueOf(personnelId);
		String sql = "select count(*) from wf_work w where w.company_id = ?  ";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			sql += " and w.app_id =?";
		}
		sql += workTitle + " and w.work_id in("
				+ "  select t.work_id from wf_hist_todo t where t.to_user = ? and t.from_user is not null) ";
		if (StringUtils.isNotBlank(isExpand) && isExpand.equals("yes")) {
			sql += " and w.app_id not in (select a.app_id from wf_application a where a.is_inherent ='1' and a.company_id='"
					+ comId + "')";
		}
		sql += " order by w.start_time desc";
		List list = new ArrayList<Work>();
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			list = createSqlQuery(sql, new Object[] { comId, appId, perId }).list();
		} else {
			list = createSqlQuery(sql, new Object[] { comId, perId }).list();
		}
		if (list.size() > 0) {
			java.math.BigDecimal count = (java.math.BigDecimal) list.get(0);
			return count.intValue();
		}
		return 0;
	}

	public Work getWorkById(String workId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "w.companyId='" + comId + "' and ";

		String hql = "from Work w where " + condition + " w.workId = ?";
		List<Work> listWork = createQuery(hql, new Object[] { workId }).list();
		if (listWork != null && listWork.size() > 0) {
			return listWork.get(0);
		}
		return null;
	}

	/**
	 * 根据应用ID与用户ID取得起草信息
	 *
	 * @param appId
	 * @param userId
	 * @return
	 */
	public List<Work> getHisttodoByAppId(String userId, String appId, int start, int max) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "c.companyId='" + comId + "' and ";

		String hql = "from Work c where " + condition + " c.starter = ? and c.appId in (" + appId
				+ ") and c.workState <> ? order by c.startTime desc";
		List<Work> list = createQuery(hql, userId, Work.STATE_START).setFirstResult(start).setMaxResults(max).list();
		if (list.size() > 0) {
			return list;
		} else {
			return null;
		}
	}

	/**
	 * 根据应用ID与用户ID取得草稿信息
	 *
	 * @param appId
	 * @param userId
	 * @return
	 */
	public List<Work> getWorkByAppId(String userId, String appId, int start, int max) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "c.companyId='" + comId + "' and ";

		String hql = "from Work c where " + condition + " c.starter = ? and c.appId in(" + appId
				+ ") and c.workState = ? order by c.startTime desc";
		List<Work> list = createQuery(hql, userId, Work.STATE_START).setFirstResult(start).setMaxResults(max).list();
		if (list.size() > 0) {
			return list;
		} else {
			return null;
		}
	}

	public List<Work> listExtHistWorks(String userId, String appId, int start, int max) {

		String sql = null;
		if (appId != null && !"".equals(appId.trim()) && !"null".equals(appId)) {
			sql = "select * from wf_work w  where  w.work_state <>'" + Work.STATE_START
					+ "' and w.app_id='" + appId + "' and"
					+ " exists (select  t.work_id from wf_hist_todo t where t.to_user = ? and t.work_id=w.work_id )"
					+ " order by w.start_time desc";
		} else {
			sql = "select * from wf_work w  where w.work_state<>'" + Work.STATE_START
					+ "' and  exists (select  t.work_id from wf_hist_todo t where t.to_user = ? and t.work_id=w.work_id )"
					+ " and exists (select 1 from wf_application a where a.is_inherent='0' and a.app_id=w.app_id ) "
					+ " order by w.start_time desc";
		}

		List<Work> list = createSqlQuery(sql, userId).addEntity(Work.class).setFirstResult(start)
				.setMaxResults(max).list();

		return list;
	}

	public List<Work> listExtDraftWorks(String userId, String appId, int start, int max) {

		String sql = null;
		if (appId != null && !"".equals(appId.trim()) && !"null".equals(appId)) {
			sql = "select * from wf_work w  where  w.starter= ? and w.work_state='" + Work.STATE_START
					+ "' and w.app_id='" + appId + "' order by w.start_time desc";
		} else {
			sql = "select * from wf_work w  where  w.starter= ? and w.work_state='" + Work.STATE_START
					+ "' and exists (select 1 from wf_application a where a.is_inherent='0' and a.app_id=w.app_id ) "
					+ " order by w.start_time desc";
		}

		List<Work> list = createSqlQuery(sql, userId).addEntity(Work.class).setFirstResult(start).setMaxResults(max)
				.list();

		return list;
	}

	public List<Work> listExtMytWorks(String userId, String appId, int start, int max) {

		String sql = null;
		if (appId != null && !"".equals(appId.trim()) && !"null".equals(appId)) {
			sql = "select * from wf_work w  where  w.starter= ? and w.work_state<>'" + Work.STATE_START
					+ "' and w.app_id='" + appId + "' order by w.start_time desc";
		} else {
			sql = "select * from wf_work w  where  w.starter= ? and w.work_state<>'" + Work.STATE_START
					+ "' and exists (select 1 from wf_application a where a.is_inherent='0' and a.app_id=w.app_id ) "
					+ " order by w.start_time desc";
		}

		List<Work> list = createSqlQuery(sql, userId).addEntity(Work.class).setFirstResult(start).setMaxResults(max)
				.list();

		return list;
	}



	/**
	 * 批量删除
	 * @author zhangyong
	 * @date   2014-12-2 上午11:48:03
	 * @param delIdsStr
	 */
	public void deleteWorks(String delIdsStr) {
		logger.debug("要批量删除的人员id字符串>>>>>>>>>>>>>"+delIdsStr);
		String sql = "delete from wf_work where work_id in   ("+delIdsStr+") ";
		createSqlQuery(sql).executeUpdate();

	}


	/**
	 * 查询起草人
	 * @author LCX
	 * @date   20141211
	 * @param workId
	 */
	public String getStarterName(String workId) {
		logger.debug("起草人workId>>>>>>>>>>>>>"+workId);
		String sql = "select starter_name from wf_work where work_id = '"+workId+"'";
		String starterName = (String)createSqlQuery(sql).uniqueResult();
		return starterName;

	}

	/**
	 *
	 * 建立日期 : 2015-1-8 下午4:43:11<br>
	 * 作者 : LCX<br>
	 * 模块 : <br>
	 * 描述 : <br>
	 * 修改历史: 序号 日期 修改人 修改原因 <br>
	 * @param delIdsStr
	 * 1 <br>
	 * 2 <br>
	 */
	public void updateWorksDeteleStatus(String delIdsStr,boolean flag) {
		String sql = "";
		if(flag){
			sql = "update wf_work set is_delete = 'delete'  where work_id in  ("+delIdsStr+") ";
		}else{
			sql = "update wf_work set is_delete = null  where work_id in  ("+delIdsStr+") ";
		}
		createSqlQuery(sql).executeUpdate();

	}
	/**
	 *
	 * 作者：wangxufeng
	 * 日期：2015-2-26
	 * 作用：更新工作的状态
	 *
	 */
	public void endWorks(String endIdsStr) {
		Date endTime = new Date();
		String workState = Work.STATE_END;
		String curActivityName ="[结束]";
		String hql = "update Work w set w.endTime=?,w.workState=?,w.curActivityName=? where w.workId in ("+endIdsStr+")";
		createQuery(hql, new Object[]{endTime,workState,curActivityName}).executeUpdate();
	}
	/**
	 * 分页查询正在办理的工作或已结束工作
	 * @author 刘杰
	 * @date   2016年1月8日 上午11:01:37
	 * @param userId
	 * @param workTitle
	 * @param workType
	 * @param state
	 * @return
	 */
	public List<Work> getHaveTodoRunWorkPage(String userId, String workTitle,
			String workType, String state) {
		String comId = AppContext.getCurrentComId();
		String hql = "from Work w where w.companyId = ?"
				+ " and w.workId in( select t.workId from HistTodo t where t.toUser = ?) ";
		if("ticket".equalsIgnoreCase(workType)){
			hql += " and w.appId not in (select a.appId from Application a where (a.appAttr='1' or a.appAttr='3' or a.appAttr='5' ) and a.isValid='1' )";
		}else if("document".equalsIgnoreCase(workType)){
			hql += " and w.appId in (select a.appId from Application a where (a.appAttr='1' or a.appAttr='3' or a.appAttr='5' )  and a.isValid='1' )";
		}
		if (workTitle!=null) {
			hql+=" and w.workTitle like '%" + workTitle + "%'";
		}
		if(StringUtils.isNotBlank(state)){
			hql+=" and w.workState='"+state+"'";
		}
		hql += " order by w.startTime desc";
		List<Work> workList = this.findPageList(hql, comId, userId);
		return workList;
	}

	/**
	 * 根据appId分页查询正在办理的工作或已结束工作
	 * @author 刘杰
	 * @date   2016年1月8日 上午11:01:45
	 * @param userId
	 * @param workTitle
	 * @param workType
	 * @param appId
	 * @param state
	 * @return
	 */
	public List<Work> getHaveTodoRunWorkPage(String userId, String workTitle,
			String workType, String appId, String state) {
		String comId = AppContext.getCurrentComId();
		String hql = "from Work w where w.companyId = ?"
				+ " and w.workId in( select t.workId from HistTodo t where t.toUser = ?) ";
		if("ticket".equalsIgnoreCase(workType)){
			hql += " and w.appId in (select a.appId from Application a where (a.appAttr='0' or a.appAttr='2' or a.appAttr='4' )  and a.isValid='1' )";
		}else if("document".equalsIgnoreCase(workType)){
			hql += " and w.appId in (select a.appId from Application a where (a.appAttr='1' or a.appAttr='3' or a.appAttr='5' )  and a.isValid='1' )";
		}
		if(StringUtils.isNotBlank(appId)){
			if(appId.indexOf(",")==-1){
				hql+=" and w.appId = '"+appId+"' ";
			}else{
				hql+=" and w.appId in ("+appId+") ";
			}
		}
		if (workTitle!=null) {
			hql+=" and w.workTitle like '%" + workTitle + "%'";
		}
		if(StringUtils.isNotBlank(state)){
			hql+=" and w.workState='"+state+"'";
		}
		hql += " order by w.startTime desc";
		List<Work> workList = this.findPageList(hql, comId, userId);
		return workList;
	}

	/**
	 * 首页查询正在办理的工作
	 * @author 刘杰
	 * @date   2016年1月8日 下午2:09:18
	 * @param userId
	 * @param num
	 * @param state
	 * @return
	 */
	public List<Work> getHaveTodoRunWorkByPid(String userId, int num, String state) {
		String comId = AppContext.getCurrentComId();
		String hql = "from Work w where w.companyId = ?"
				+ " and w.workId in( select t.workId from HistTodo t where t.toUser = ?) ";

		if(StringUtils.isNotBlank(state)){
			hql+=" and w.workState='"+state+"'";
		}
		hql += " order by w.startTime desc";
		List<Work> workList = createQuery(hql,comId,userId).setFirstResult(0).setMaxResults(num).list();
		return workList;
	}


}
